/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bean;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 *
 * @author Ronny
 */
public class ProsesBean {
    //koneksi jdbc
//
//    private String jdbcURL = "jdbc:oracle:thin:@localhost:1521:xe";
//    private String user = "hr";
//    private String password = "hr";
    
    private String jdbcURL = "jdbc:oracle:thin:@172.23.9.185:1521:orcl";
    private String user = "MHS105314096";
    private String password = "MHS105314096";
    private Connection connection = null;
 
//    private String jdbcURL = "jdbc:oracle:thin:@localhost:1521:xe";
//    private String user = "rplo";
//    private String password = "oracle";
//    private Connection connection = null;
    
    public ProsesBean() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            connection = DriverManager.getConnection(jdbcURL, user, password);
        } catch (Exception ex) {
            System.out.println("message: " + ex.getMessage());
        }
    }

    public boolean isConnected() {
        if (connection != null) {
            return true;
        } else {
            return false;
        }
    }

    public boolean getClosed() {
        if (isConnected()) {
            try {
                connection.close();
                connection = null;
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return true;
        } else {
            return false;
        }
    }

    public String getJdbcURL() {
        return jdbcURL;
    }

    public void setJdbcURL(String jdbcURL) {
        this.jdbcURL = jdbcURL;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Connection getConnection() {
        return connection;
    }

    public void setConn(Connection connection) {
        this.connection = connection;
    }

    //Proses Login user Guru
    public ResultSet loginGuru(String user, String passwrd) throws SQLException {
        Statement statement = connection.createStatement();
        String query = "select username,password from usersistem where username='" + user + "'and password= '" + passwrd + "' and status='guru'";
        System.out.println(user);
        System.out.println(passwrd);
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;
    }
    //Proses Login user wali murid

    public ResultSet loginWali(String user, String passwrd) throws SQLException {
        Statement statement = connection.createStatement();
        String query = "select username,password from usersistem where username='" + user + "'and password= '" + passwrd + "' and status='wali'";
        System.out.println(user);
        System.out.println(passwrd);
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;
    }
    //Proses Login user TU

    public ResultSet loginTu(String user, String passwrd) throws SQLException {
        Statement statement = connection.createStatement();
        String query = "select username,password from usersistem where username='" + user + "'and password= '" + passwrd + "' and status='tu'";
        System.out.println(user);
        System.out.println(passwrd);
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;
    }

    //Input Username
    public void inputUsername(String username, String password, String status) throws SQLException {
        try {
            Statement statement = connection.createStatement();
            String query = "insert into usersistem  values('" + username + "', '"
                    + password + "', '" + status + "')";
            int ok = statement.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(ProsesBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    //Input Data Karyawan
    public void inputKaryawan(String kode, String nama, String jabatan, String username) throws SQLException {
        try {
            Statement statement = connection.createStatement();
            String query = "insert into karyawan  values('" + kode + "', '"
                    + nama + "', '" + jabatan + "','" + username + "')";
            int ok = statement.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(ProsesBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    //Get Data guru
    public ResultSet getGuru() throws SQLException {
        Statement statement = connection.createStatement();
        String query = "select kodekaryawan from karyawan where jabatan='guru'";
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;
    }

    //Input Data Mapel
    public void inputMapel(String kode, String nama, String ampu) throws SQLException {
        try {
            Statement statement = connection.createStatement();
            String query = "insert into matapelajaran  values('" + kode + "', '"
                    + nama + "','" + ampu + "')";
            int ok = statement.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(ProsesBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    //input data siswa
    public void inputSiswa(String nis, String nama, String kelas, String alamat, String telp, String username) throws SQLException {
        try {
            Statement statement = connection.createStatement();
            String query = "insert into walimurid values('" + nis + "', '"
                    + nama + "','" + kelas + "','" + alamat + "','" + telp + "','" + username + "')";
            int ok = statement.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(ProsesBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void inputUserWali(String username, String password) throws SQLException {
        try {
            Statement statement = connection.createStatement();
            String query = "insert into usersistem values('" + username + "','"
                    + password + "', 'wali')";
            int ok = statement.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(ProsesBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    //Proses Lihat nilai siswa
    public ResultSet mengambilNilaiSiswa(String username) throws SQLException
    {
        Statement statement = connection.createStatement();


        String query = "select a.namamapel, b.ulangan1, b.ulangan2, b.ulangan3, b.tugas1, b.tugas2, b.midsemester, b.semester, b.nilaiakhir "
                        + " from matapelajaran a join nilai b on a.kodemapel = b.kodemapel " 
                        + " where b.kodesiswa = (select kodesiswa from walimurid where username = '"+username+"')";  

        System.out.println(username);

        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;
    }
    
    //Proses Lihat daftar Mapel
    public ResultSet lihatDaftarMapel(String username) throws SQLException
    {
        Statement statement = connection.createStatement();
        String query = "select kodemapel, namamapel from matapelajaran "
                + "where pengampu = (select kodekaryawan from karyawan where username='"+username+"')";  
        System.out.println(username);
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;
    }
    
    //Method lihat nilai kelas
    public ResultSet dataNilaiKelas(String kodemapel) throws SQLException {
        Statement statement = connection.createStatement();
        String query = "select a.kodesiswa, a.namasiswa, b.ulangan1, b.ulangan2, b.ulangan3, b.tugas1, b.tugas2, b.midsemester, b.semester, b.nilaiakhir " 
                        + "from walimurid a join nilai b on a.kodesiswa = b.kodesiswa "
                        + "where b.kodemapel = '"+kodemapel+"'";
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;
    }
    public ResultSet getdatasiswa(String username) throws SQLException
    {
        Statement statement = connection.createStatement();
        String query = "select kodesiswa, namasiswa, kelas, alamat, telpon "
                + " from walimurid where username = '"+username+"'";
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;
    }
    //Method ulangan 1
       public void UPLOAD_ULANGAN1(String namafile) throws FileNotFoundException, IOException, SQLException {
        System.out.println(namafile);
        FileInputStream fileInputStream = new FileInputStream(namafile);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet worksheet = workbook.getSheet("Sheet1");
//matapelajran
        HSSFRow mataklh = worksheet.getRow(0);
        HSSFCell nama = mataklh.getCell(1);
        System.out.println(nama.getStringCellValue());
        //jumlah siswa
        HSSFRow jumlh = worksheet.getRow(1);
        HSSFCell jumlhcell = jumlh.getCell(1);
        Double cell = jumlhcell.getNumericCellValue();
        System.out.println(cell);

        for (int i = 4; i < cell.intValue() +4; i++) {
            HSSFRow row1 = worksheet.getRow(i);
            HSSFCell cellC1 = row1.getCell((short) 0);
            double kodesiswa = cellC1.getNumericCellValue();

            HSSFRow row2 = worksheet.getRow(i);
            HSSFCell cellC2 = row2.getCell((short) 1);
            double kodemapel = cellC2.getNumericCellValue();

            HSSFRow row3 = worksheet.getRow(i);
            HSSFCell cellC3 = row3.getCell((short) 2);
            double nilai = cellC3.getNumericCellValue();

            System.out.println("A1: " + kodesiswa + "        A2:" + kodemapel + "     a3: " + nilai);

            Statement statement = connection.createStatement();
            String query = "insert into nilai(ulangan1, kodemapel, kodesiswa) values("+nilai+","+kodemapel+","+kodesiswa+")";
            int ok = statement.executeUpdate(query);

        }
    }

       //Method ulangan 1
       public void UPLOAD_ULANGAN2(String namafile) throws FileNotFoundException, IOException, SQLException {
        System.out.println(namafile);
        FileInputStream fileInputStream = new FileInputStream(namafile);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet worksheet = workbook.getSheet("Sheet1");
//matapelajran
        HSSFRow mataklh = worksheet.getRow(0);
        HSSFCell nama = mataklh.getCell(1);
        System.out.println(nama.getStringCellValue());
        //jumlah siswa
        HSSFRow jumlh = worksheet.getRow(1);
        HSSFCell jumlhcell = jumlh.getCell(1);
        Double cell = jumlhcell.getNumericCellValue();
        System.out.println(cell);

        for (int i = 4; i < cell.intValue() +4; i++) {
            HSSFRow row1 = worksheet.getRow(i);
            HSSFCell cellC1 = row1.getCell((short) 0);
            double kodesiswa = cellC1.getNumericCellValue();

            HSSFRow row2 = worksheet.getRow(i);
            HSSFCell cellC2 = row2.getCell((short) 1);
            double kodemapel = cellC2.getNumericCellValue();

            HSSFRow row3 = worksheet.getRow(i);
            HSSFCell cellC3 = row3.getCell((short) 2);
            double nilai = cellC3.getNumericCellValue();

            System.out.println("A1: " + kodesiswa + "        A2:" + kodemapel + "     a3: " + nilai);

            Statement statement = connection.createStatement();
            String query = "update nilai set ulangan2 =" + nilai + " where kodemapel=" + kodemapel + " and kodesiswa=" + kodesiswa + "";
            int ok = statement.executeUpdate(query);

        }
    }

       //Method ulangan 1
       public void UPLOAD_ULANGAN3(String namafile) throws FileNotFoundException, IOException, SQLException {
        System.out.println(namafile);
        FileInputStream fileInputStream = new FileInputStream(namafile);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet worksheet = workbook.getSheet("Sheet1");
//matapelajran
        HSSFRow mataklh = worksheet.getRow(0);
        HSSFCell nama = mataklh.getCell(1);
        System.out.println(nama.getStringCellValue());
        //jumlah siswa
        HSSFRow jumlh = worksheet.getRow(1);
        HSSFCell jumlhcell = jumlh.getCell(1);
        Double cell = jumlhcell.getNumericCellValue();
        System.out.println(cell);

        for (int i = 4; i < cell.intValue() +4; i++) {
            HSSFRow row1 = worksheet.getRow(i);
            HSSFCell cellC1 = row1.getCell((short) 0);
            double kodesiswa = cellC1.getNumericCellValue();

            HSSFRow row2 = worksheet.getRow(i);
            HSSFCell cellC2 = row2.getCell((short) 1);
            double kodemapel = cellC2.getNumericCellValue();

            HSSFRow row3 = worksheet.getRow(i);
            HSSFCell cellC3 = row3.getCell((short) 2);
            double nilai = cellC3.getNumericCellValue();

            System.out.println("A1: " + kodesiswa + "        A2:" + kodemapel + "     a3: " + nilai);

            Statement statement = connection.createStatement();
            String query = "update nilai set ulangan3 =" + nilai + " where kodemapel=" + kodemapel + " and kodesiswa=" + kodesiswa + "";
            int ok = statement.executeUpdate(query);

        }
    }

       public void UPLOAD_TUGAS1(String namafile) throws FileNotFoundException, IOException, SQLException {
        System.out.println(namafile);
        FileInputStream fileInputStream = new FileInputStream(namafile);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet worksheet = workbook.getSheet("Sheet1");
//matapelajran
        HSSFRow mataklh = worksheet.getRow(0);
        HSSFCell nama = mataklh.getCell(1);
        System.out.println(nama.getStringCellValue());
        //jumlah siswa
        HSSFRow jumlh = worksheet.getRow(1);
        HSSFCell jumlhcell = jumlh.getCell(1);
        Double cell = jumlhcell.getNumericCellValue();
        System.out.println(cell);

        for (int i = 4; i < cell.intValue() +4; i++) {
            HSSFRow row1 = worksheet.getRow(i);
            HSSFCell cellC1 = row1.getCell((short) 0);
            double kodesiswa = cellC1.getNumericCellValue();

            HSSFRow row2 = worksheet.getRow(i);
            HSSFCell cellC2 = row2.getCell((short) 1);
            double kodemapel = cellC2.getNumericCellValue();

            HSSFRow row3 = worksheet.getRow(i);
            HSSFCell cellC3 = row3.getCell((short) 2);
            double nilai = cellC3.getNumericCellValue();

            System.out.println("A1: " + kodesiswa + "        A2:" + kodemapel + "     a3: " + nilai);

            Statement statement = connection.createStatement();
            String query = "update nilai set tugas1 =" + nilai + " where kodemapel=" + kodemapel + " and kodesiswa=" + kodesiswa + "";
            int ok = statement.executeUpdate(query);

        }
    }

       public void UPLOAD_TUGAS2(String namafile) throws FileNotFoundException, IOException, SQLException {
        System.out.println(namafile);
        FileInputStream fileInputStream = new FileInputStream(namafile);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet worksheet = workbook.getSheet("Sheet1");
//matapelajran
        HSSFRow mataklh = worksheet.getRow(0);
        HSSFCell nama = mataklh.getCell(1);
        System.out.println(nama.getStringCellValue());
        //jumlah siswa
        HSSFRow jumlh = worksheet.getRow(1);
        HSSFCell jumlhcell = jumlh.getCell(1);
        Double cell = jumlhcell.getNumericCellValue();
        System.out.println(cell);

        for (int i = 4; i < cell.intValue() +4; i++) {
            HSSFRow row1 = worksheet.getRow(i);
            HSSFCell cellC1 = row1.getCell((short) 0);
            double kodesiswa = cellC1.getNumericCellValue();

            HSSFRow row2 = worksheet.getRow(i);
            HSSFCell cellC2 = row2.getCell((short) 1);
            double kodemapel = cellC2.getNumericCellValue();

            HSSFRow row3 = worksheet.getRow(i);
            HSSFCell cellC3 = row3.getCell((short) 2);
            double nilai = cellC3.getNumericCellValue();

            System.out.println("A1: " + kodesiswa + "        A2:" + kodemapel + "     a3: " + nilai);

            Statement statement = connection.createStatement();
            String query = "update nilai set tugas2 =" + nilai + " where kodemapel=" + kodemapel + " and kodesiswa=" + kodesiswa + "";
            int ok = statement.executeUpdate(query);

        }
    }

       public void UPLOAD_MIDSEMESTER(String namafile) throws FileNotFoundException, IOException, SQLException {
        System.out.println(namafile);
        FileInputStream fileInputStream = new FileInputStream(namafile);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet worksheet = workbook.getSheet("Sheet1");
//matapelajran
        HSSFRow mataklh = worksheet.getRow(0);
        HSSFCell nama = mataklh.getCell(1);
        System.out.println(nama.getStringCellValue());
        //jumlah siswa
        HSSFRow jumlh = worksheet.getRow(1);
        HSSFCell jumlhcell = jumlh.getCell(1);
        Double cell = jumlhcell.getNumericCellValue();
        System.out.println(cell);

        for (int i = 4; i < cell.intValue() +4; i++) {
            HSSFRow row1 = worksheet.getRow(i);
            HSSFCell cellC1 = row1.getCell((short) 0);
            double kodesiswa = cellC1.getNumericCellValue();

            HSSFRow row2 = worksheet.getRow(i);
            HSSFCell cellC2 = row2.getCell((short) 1);
            double kodemapel = cellC2.getNumericCellValue();

            HSSFRow row3 = worksheet.getRow(i);
            HSSFCell cellC3 = row3.getCell((short) 2);
            double nilai = cellC3.getNumericCellValue();

            System.out.println("A1: " + kodesiswa + "        A2:" + kodemapel + "     a3: " + nilai);

            Statement statement = connection.createStatement();
            String query = "update nilai set midsemester =" + nilai + " where kodemapel=" + kodemapel + " and kodesiswa=" + kodesiswa + "";
            int ok = statement.executeUpdate(query);

        }
    }

       public void UPLOAD_SEMESTER(String namafile) throws FileNotFoundException, IOException, SQLException {
        System.out.println(namafile);
        FileInputStream fileInputStream = new FileInputStream(namafile);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet worksheet = workbook.getSheet("Sheet1");
//matapelajran
        HSSFRow mataklh = worksheet.getRow(0);
        HSSFCell nama = mataklh.getCell(1);
        System.out.println(nama.getStringCellValue());
        //jumlah siswa
        HSSFRow jumlh = worksheet.getRow(1);
        HSSFCell jumlhcell = jumlh.getCell(1);
        Double cell = jumlhcell.getNumericCellValue();
        System.out.println(cell);

        for (int i = 4; i < cell.intValue() +4; i++) {
            HSSFRow row1 = worksheet.getRow(i);
            HSSFCell cellC1 = row1.getCell((short) 0);
            double kodesiswa = cellC1.getNumericCellValue();

            HSSFRow row2 = worksheet.getRow(i);
            HSSFCell cellC2 = row2.getCell((short) 1);
            double kodemapel = cellC2.getNumericCellValue();

            HSSFRow row3 = worksheet.getRow(i);
            HSSFCell cellC3 = row3.getCell((short) 2);
            double nilai = cellC3.getNumericCellValue();

            System.out.println("A1: " + kodesiswa + "        A2:" + kodemapel + "     a3: " + nilai);

            Statement statement = connection.createStatement();
            String query = "update nilai set semester =" + nilai + " where kodemapel=" + kodemapel + " and kodesiswa=" + kodesiswa + "";
            int ok = statement.executeUpdate(query);

        }
    }

       public void UPLOAD_NILAIAKHIR(String namafile) throws FileNotFoundException, IOException, SQLException {
        System.out.println(namafile);
        FileInputStream fileInputStream = new FileInputStream(namafile);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet worksheet = workbook.getSheet("Sheet1");
//matapelajran
        HSSFRow mataklh = worksheet.getRow(0);
        HSSFCell nama = mataklh.getCell(1);
        System.out.println(nama.getStringCellValue());
        //jumlah siswa
        HSSFRow jumlh = worksheet.getRow(1);
        HSSFCell jumlhcell = jumlh.getCell(1);
        Double cell = jumlhcell.getNumericCellValue();
        System.out.println(cell);

        for (int i = 4; i < cell.intValue() +4; i++) {
            HSSFRow row1 = worksheet.getRow(i);
            HSSFCell cellC1 = row1.getCell((short) 0);
            double kodesiswa = cellC1.getNumericCellValue();

            HSSFRow row2 = worksheet.getRow(i);
            HSSFCell cellC2 = row2.getCell((short) 1);
            double kodemapel = cellC2.getNumericCellValue();

            HSSFRow row3 = worksheet.getRow(i);
            HSSFCell cellC3 = row3.getCell((short) 2);
            double nilai = cellC3.getNumericCellValue();

            System.out.println("A1: " + kodesiswa + "        A2:" + kodemapel + "     a3: " + nilai);

            Statement statement = connection.createStatement();
            String query = "update nilai set nilaiakhir =" + nilai + " where kodemapel=" + kodemapel + " and kodesiswa=" + kodesiswa + "";
            int ok = statement.executeUpdate(query);

        }
    }
}
